package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Custom;
import cn.gson.crm.vo.CustomVo;
import cn.gson.crm.vo.EmployeeVo;

public class CustomDao {
	private int pageSize = 7;

	// ===============================
	// 获得学历信息
	public List<Map<String, Object>> getDegreeAll() throws SQLException {
		String sql = "SELECT * FROM degree";
		List<Map<String, Object>> map = Jdbc.getInstace().executeQueryForList(sql);
		return map;
	}

	// ===============================
	/**
	 * 带条件的分页查询
	 * 
	 * @param type
	 * @param page
	 * @param userName
	 * @param dept
	 * @param tel
	 * @return
	 * @throws SQLException
	 */
	public List<CustomVo> getEmpAllList(int type, int page, String s) throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "SELECT customer.*, degree_name FROM customer INNER JOIN degree ON customer.cust_degree_id = degree.degree_id WHERE ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, type, sql, s);
		sql += " order by cust_id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
//		 System.out.println(sql);
		return queryUserList(sql, params.toArray());
	}

	private String buildSqlAndParams(List<Object> params, int type, String sql, String s) {

		if (StringUtils.isNullOrEmpty(s)) {
			params.add(type);
			sql += " cust_counselor_id is null and cust_status_id = ? ";
		}
		if (!StringUtils.isNullOrEmpty(s)) {
			sql += " cust_counselor_id IS NULL AND cust_status_id = 1 and cust_name like ? ";
			params.add("%" + s + "%");
		}
		if (!StringUtils.isNullOrEmpty(s)) {
			sql += " or cust_counselor_id IS NULL AND cust_status_id = 1 and degree_name like ? ";
			params.add("%" + s + "%");
		}
		if (!StringUtils.isNullOrEmpty(s)) {
			sql += " or cust_counselor_id IS NULL AND cust_status_id = 1 and cust_source like ? ";
			params.add("%" + s + "%");
		}
		if (!StringUtils.isNullOrEmpty(s)) {
			sql += " or cust_counselor_id IS NULL AND cust_status_id = 1 and cust_telphone like ? ";
			params.add("%" + s + "%");
		}
		if (!StringUtils.isNullOrEmpty(s)) {
			sql += " or cust_counselor_id IS NULL AND cust_status_id = 1 and cust_qq like ? ";
			params.add("%" + s + "%");
		}
		if (!StringUtils.isNullOrEmpty(s)) {
			sql += " or cust_counselor_id IS NULL AND cust_status_id = 1 and cust_gender like ? ";
			params.add("%" + s + "%");
		}
		if (!StringUtils.isNullOrEmpty(s)) {
			sql += " or cust_counselor_id IS NULL AND cust_status_id = 1 and cust_address like ? ";
			params.add("%" + s + "%");
		}
		return sql;
	}

	private List<CustomVo> queryUserList(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> listData = Jdbc.getInstace().executeQueryForList(sql, params);
		List<CustomVo> customVoList = null;
		if (listData != null && listData.size() > 0) {
			customVoList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				customVoList.add(builderCustom(data));
			}
		}
		return customVoList;
	}

	/**
	 * 搜索总页数
	 * 
	 * @param type
	 * @param userName
	 * @param dept
	 * @param tel
	 * @return
	 * @throws SQLException
	 */
	public int getTotalPage(int type, String s) throws SQLException {
		// String sql = "select count(*) total from customer where
		// cust_counselor_id is null and cust_status_id = ? ";
		String sql = "SELECT count(degree_name)total FROM customer INNER JOIN degree ON customer.cust_degree_id = degree.degree_id WHERE ";
		// cust_counselor_id is null and cust_status_id = ?"
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, type, sql, s);
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}

	/**
	 * 获得所有的客户信息，不带查询
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<CustomVo> getAllCustomByStatus(int type, int page) throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "SELECT customer.*,degree.degree_name FROM customer INNER JOIN degree ON customer.cust_degree_id=degree.degree_id WHERE customer.cust_counselor_id is null and cust_status_id=? limit ?,?";
		List<Object> params = new ArrayList<>();
		params.add(type);
		params.add(start);
		params.add(pageSize);
		return queryUserList(sql, params.toArray());
	}

	// 获得具体的状态的客户的分页，不带查询
	public int getTotalPage(int type) throws SQLException {
		String sql = "SELECT COUNT(*) total FROM customer WHERE cust_status_id=?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, type);
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}

	/**
	 * 分配客户到咨询师跟进
	 * 
	 * @param counselorId
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean assignCustom(String counselorId, String id) throws SQLException {
		String sql = "UPDATE customer SET cust_counselor_id=? WHERE cust_id=?";
		boolean result = Jdbc.getInstace().executeUpdate(sql, counselorId, id);
		return result;
	}

	/**
	 * 根据id查找客户
	 * 
	 * @return
	 * @throws SQLException
	 */
	public CustomVo getOneCustomByid(String id) throws SQLException {
		String sql = "SELECT customer.*, degree_name FROM customer INNER JOIN degree ON customer.cust_degree_id = degree.degree_id WHERE cust_id=?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, id);
		CustomVo customVo = null;
		if (map != null || !map.isEmpty()) {
			customVo = builderCustom(map);
		}
		return customVo;
	}

	/**
	 * 根据id查看或者更新客户信息
	 * 
	 * @param custom
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean updateCustomById(Custom c, String id) throws SQLException {
		String sql = "UPDATE customer SET cust_name =?, cust_gender =?, "
				+ "cust_address =?, cust_telphone =?,cust_degree_id =?,cust_qq=?, cust_parentname =?, "
				+ "cust_parentTel =?, cust_source =?,cust_counselor_id =? WHERE cust_id =?";
		boolean result = resultCustom(sql, c, id);
		return result;
	}

	/**
	 * 更改客户状态
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean updateCustomStatus(int status,String id) throws SQLException {
		String sql = "UPDATE customer set cust_status_id = ? where cust_id = ?";
		boolean result = Jdbc.getInstace().executeUpdate(sql,status, id);
		return result;

	}

	/**
	 * 新建一个客户
	 * 
	 * @return
	 * @throws SQLException
	 */
	public boolean insertCustom(Custom c) throws SQLException {
		String sql = "INSERT INTO customer (cust_name,cust_gender,"
				+ "cust_address,cust_telphone,cust_degree_id,cust_qq," + "cust_parentname,cust_parentTel,cust_source,"
				+ "cust_counselor_id,cust_status_id) VALUES (?,?,?,?,?,?,?,?,?,?,'1')";
		boolean result = resultCustom(sql, c);
		return result;
	}

	/**
	 * 删除一条客户信息
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean daleteCustom(String id) throws SQLException {
		String sql = "UPDATE customer SET cust_status_id=10 WHERE cust_id=?";
		// String sql = "DELETE FROM customer WHERE cust_id=?";
		boolean result = Jdbc.getInstace().executeUpdate(sql, id);
		return result;
	}

	/**
	 * 封装增删改的语句,方法重载
	 * 
	 * @param sql
	 * @param c
	 * @param object
	 * @return
	 * @throws SQLException
	 */
	private boolean resultCustom(String sql, Custom c, String id) throws SQLException {
		boolean result = Jdbc.getInstace().executeUpdate(sql, c.getCustomName(), c.getCustomGender(),
				c.getCustomAddress(), c.getCustomTel(), c.getCustomDegreeId(), c.getCustomQQ(), c.getCustomParentName(),
				c.getCustomParentTel(), c.getCustomSource(), c.getCustomCounselorId(), id);
		return result;
	}

	private boolean resultCustom(String sql, Custom c) throws SQLException {
		boolean result = Jdbc.getInstace().executeUpdate(sql, c.getCustomName(), c.getCustomGender(),
				c.getCustomAddress(), c.getCustomTel(), c.getCustomDegreeId(), c.getCustomQQ(), c.getCustomParentName(),
				c.getCustomParentTel(), c.getCustomSource(), c.getCustomCounselorId());
		return result;
	}

	private CustomVo builderCustom(Map<String, Object> map) {
		CustomVo customVo = new CustomVo();
		customVo.setCustomId((Integer) map.get("cust_id"));
		customVo.setCustomName((String) map.get("cust_name"));
		customVo.setCustomGender((String) map.get("cust_gender"));
		customVo.setCustomAddress((String) map.get("cust_address"));
		customVo.setCustomTel((String) map.get("cust_telphone"));
		customVo.setCustomDegreeId((Integer) map.get("cust_degree_id"));
		customVo.setCustomQQ((String) map.get("cust_qq"));
		customVo.setCustomParentName((String) map.get("cust_parentname"));
		customVo.setCustomParentTel((String) map.get("cust_parentTel"));
		customVo.setCustomSource((String) map.get("cust_source"));
		customVo.setCustomCounselorId((Integer) map.get("cust_counselor_id"));
		customVo.setCustomCreatetime((Date) map.get("cust_createtime"));
		customVo.setCustomDrividetime((Date) map.get("cust_drividetime"));
		customVo.setCustomStatusId((Integer) map.get("cust_status_id"));
		customVo.setDegreeName((String) map.get("degree_name"));
		return customVo;

	}
}
